package com.mingle.iwalk;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.provider.BaseColumns;

public class TravelerDB extends SQLiteOpenHelper implements BaseColumns{
	
	private static final String DATABASE_NAME 		= "traveler.db";
    private static final int 	DATABASE_VERSION 	= 2;
    private static final String POIS_TABLE_NAME 	= "POIs";
    private static final String TRACKS_TABLE_NAME 	= "Tracks";
    
    public static final String 	TYPE 			= "type";
    public static final String	UUID 			= "uuid";
    public static final String 	TITLE 			= "title";
    public static final String 	DESCRIPTION 	= "description";
    public static final String	UPLOAD_FLAG 	= "upload_flag";
    public static final String	UPLOAD_TIME 	= "created";
    public static final String	CREATED_TIME 	= "modified";
    public static final String	TAG 			= "tag";
    public static final String	WORKING_FOLDER_PATH= "working_folder_path";
    
    public static final String 	PICTURE_THUMB 	= "picture_thumb";
    public static final String 	PICTURE_FLAG 	= "picture_flag";
    public static final String 	PICTURE_PATH 	= "picture_path";
    public static final String 	LOCATION_TYPE 	= "location_type";
    public static final String 	LOCATION_LAT 	= "location_lat";
    public static final String 	LOCATION_LON 	= "location_lon";
    public static final String 	LOCATION_ALT 	= "location_alt";
    public static final String 	LOCATION_ACC 	= "location_acc";
    public static final String 	VOICE_FLAG 		= "voice_flag";
    public static final String 	VOICE_PATH 		= "voice_path";
    public static final String 	TRACK_ID 		= "track_id";
    
    public static final String 	TRACK_PATH 		= "track_path";
    public static final String 	TRACK_COUNT 	= "track_count";
    public static final String 	TRACK_STATE 	= "track_state";
    
	public static final int DB_SAVE_ACTION_NEW = 0;
	public static final int DB_SAVE_ACTION_UPDATE = 1;

	static private TravelerDB mThiz;
	
	//private Context mContext;
	
	public static TravelerDB getInstance(Context context)
	{
		if(mThiz == null)
		{
			mThiz = new TravelerDB(context);
		}
		return mThiz;
	}
	
	TravelerDB(Context context)
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		//mContext = context;
	}
	
	public int savePOIInfo(POIInfo info)
	{
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TravelerDB.TYPE, info.type);
		cv.put(TravelerDB.UUID, info.uuid);
		cv.put(TravelerDB.TITLE, info.title);
		cv.put(TravelerDB.DESCRIPTION, info.description);
		cv.put(TravelerDB.UPLOAD_FLAG, info.uploadFlag);
		cv.put(TravelerDB.UPLOAD_TIME, info.uploadTime);
		cv.put(TravelerDB.CREATED_TIME, info.createTime);
		cv.put(TravelerDB.TAG, info.tagType);
		cv.put(TravelerDB.WORKING_FOLDER_PATH, info.workingFolderPath);
     
		if(info.picThumb != null)
		{
			final ByteArrayOutputStream os = new ByteArrayOutputStream();
			info.picThumb.compress(Bitmap.CompressFormat.PNG, 100, os);    
			cv.put(TravelerDB.PICTURE_THUMB, os.toByteArray());
		}
		else
		{
			cv.putNull(TravelerDB.PICTURE_THUMB);
		}
		
		if(info.picPath!=null && info.picPath.length()!=0)
		{
			cv.put(TravelerDB.PICTURE_FLAG, 1);
		}
		else
		{
			cv.put(TravelerDB.PICTURE_FLAG, 0);
		}
		
		cv.put(TravelerDB.PICTURE_PATH, info.picPath);
		cv.put(TravelerDB.LOCATION_TYPE, info.locationType);
		if(info.locationType!= POIInfo.LOCATION_TYPE_UNKNOWN && info.location!=null)
		{
			cv.put(TravelerDB.LOCATION_LAT, info.location.getLatitude());
			cv.put(TravelerDB.LOCATION_LON, info.location.getLongitude());
			cv.put(TravelerDB.LOCATION_ALT, info.location.getAltitude());
			cv.put(TravelerDB.LOCATION_ACC, info.location.getAccuracy());
		}
		
		if(info.viocePath!=null && info.viocePath.length()!=0)
		{
			cv.put(TravelerDB.VOICE_FLAG, 1);
		}
		else
		{
			cv.put(TravelerDB.VOICE_FLAG, 0);
		}
		cv.put(TravelerDB.VOICE_PATH, info.viocePath);
		cv.put(TravelerDB.TRACK_ID, info.relativeTrackId);
		
		if(info.id == TravelerInfo.INFO_INVALID_ID)
		{
			return (int)db.insert(POIS_TABLE_NAME, null, cv);
		}
		else
		{
			db.update(POIS_TABLE_NAME, cv, TravelerDB._ID + "==" + info.id, null);
			return info.id;
		}
	}
	
	public int saveTrackInfo(TrackInfo info)
	{
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TravelerDB.TYPE, info.type);
		cv.put(TravelerDB.UUID, info.uuid);
		cv.put(TravelerDB.TITLE, info.title);
		cv.put(TravelerDB.DESCRIPTION, info.description);
		cv.put(TravelerDB.UPLOAD_FLAG, info.uploadFlag);
		cv.put(TravelerDB.UPLOAD_TIME, info.uploadTime);
		cv.put(TravelerDB.CREATED_TIME, info.createTime);
		cv.put(TravelerDB.TAG, info.tagType);
		cv.put(TravelerDB.WORKING_FOLDER_PATH, info.workingFolderPath);
		cv.put(TravelerDB.TRACK_COUNT, info.trackList != null?info.trackList.size():0);
		cv.put(TravelerDB.TRACK_PATH, info.trackPath);
		cv.put(TravelerDB.TRACK_STATE, info.trackState);
		
		if(info.id == TravelerInfo.INFO_INVALID_ID)
		{
			return (int)db.insert(TRACKS_TABLE_NAME, null, cv);
		}
		else
		{
			db.update(TRACKS_TABLE_NAME, cv, TravelerDB._ID + "==" + info.id, null);
			return info.id;
		}
	}
	
	public int saveTrackState(int id, int state)
	{
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TravelerDB.TRACK_STATE, state);

		db.update(TRACKS_TABLE_NAME, cv, TravelerDB._ID + "==" + id, null);
		
		return id;
	}
	
	
	public List<POIInfo> getTravelerInfoWithPic()
	{
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = db.query(POIS_TABLE_NAME, null, TravelerDB.PICTURE_FLAG + "==" + 1, null, null, null, "modified DESC");
			
		if(cursor == null)
		{
			return null;
		}
		
		List<POIInfo> listInfo = new ArrayList<POIInfo>();
		
		while(cursor.moveToNext())
	    {
			POIInfo data = new POIInfo();
			data.convert2TravelerInfo(cursor);
			listInfo.add(data);
	    }
			
		return listInfo;
	}
	
	public int saveTrackCount(int id, int count)
	{
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TravelerDB.TRACK_COUNT, count);
		
		db.update(TRACKS_TABLE_NAME, cv, TravelerDB._ID + "==" + id, null);
		
		return id;
	}
	
	public int saveUploadFlag(int type, int id, boolean flag)
	{
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TravelerDB.UPLOAD_FLAG, flag);
		
		if(type == TravelerInfo.INFO_TYPE_POI)
		{
			db.update(POIS_TABLE_NAME, cv, TravelerDB._ID + "==" + id, null);
		}
		else if(type == TravelerInfo.INFO_TYPE_TRACK)
		{
			db.update(TRACKS_TABLE_NAME, cv, TravelerDB._ID + "==" + id, null);
		}
		
		//mContext.getContentResolver().notifyChange(null, null);
		
		return id;
	}

	private TravelerInfo select(int id, int type)
	{
		TravelerInfo data = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
			
		if(type == TravelerInfo.INFO_TYPE_POI)
		{
			cursor = db.query(POIS_TABLE_NAME, null, TravelerDB._ID + "==" + id, null, null, null, null);
			data = new POIInfo();
		}
		else if(type == TravelerInfo.INFO_TYPE_TRACK)
		{
			cursor = db.query(TRACKS_TABLE_NAME, null, TravelerDB._ID + "==" + id, null, null, null, null);
			data = new TrackInfo();
		}
			
		if(cursor == null)
		{
			return null;
		}
		
		while(cursor.moveToNext())
        {
			data.convert2TravelerInfo(cursor);
        }
		
		return data;
	}
	
	public boolean deleteTravelerInfo(int id, int type)
	{
		SQLiteDatabase db = getReadableDatabase();
		if(type == TravelerInfo.INFO_TYPE_POI)
		{
			db.delete(POIS_TABLE_NAME, TravelerDB._ID + "==" + id, null);
		}
		else if(type == TravelerInfo.INFO_TYPE_TRACK)
		{
			db.delete(TRACKS_TABLE_NAME, TravelerDB._ID + "==" + id, null);
		}
		
		return true;
	}
	
	public TravelerInfo getTravelerInfo(int id, int type)
	{
		return select(id, type);
	}

	public Cursor getTravelerInfos(int type)
	{
		SQLiteDatabase db = getReadableDatabase();
		if(type == TravelerInfo.INFO_TYPE_POI)
		{
			return db.query(POIS_TABLE_NAME, null, null, null, null, null, "modified DESC");
		}
		else if(type == TravelerInfo.INFO_TYPE_TRACK)
		{
			return db.query(TRACKS_TABLE_NAME, null, null, null, null, null, "modified DESC");
		}

		return null;
	}
	
	public List<POIInfo> getRelativePOIInfo(int relativeId)
	{
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = db.query(POIS_TABLE_NAME, null, TravelerDB.TRACK_ID + "==" + relativeId, null, null, null, "modified DESC");
			
		if(cursor == null)
		{
			return null;
		}
		
		List<POIInfo> listInfo = new ArrayList<POIInfo>();
		
		while(cursor.moveToNext())
	    {
			POIInfo data = new POIInfo();
			data.convert2TravelerInfo(cursor);
			listInfo.add(data);
	    }
			
		return listInfo;
	}
	
	public Cursor getRelativePOIInfos(int relativeId, int type)
	{
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = db.query(POIS_TABLE_NAME, null, TravelerDB.TRACK_ID + "==" + relativeId, null, null, null, "modified DESC");
			
		if(cursor == null)
		{
			return null;
		}
		
		return cursor;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + POIS_TABLE_NAME + " ("
				
                + TravelerDB._ID 			+ " INTEGER PRIMARY KEY,"
                + TravelerDB.UUID 			+ " INTEGER,"
                + TravelerDB.TYPE 			+ " INTEGER,"
                + TravelerDB.TITLE 			+ " TEXT,"
                + TravelerDB.DESCRIPTION 	+ " TEXT,"
                + TravelerDB.UPLOAD_FLAG 	+ " INTEGER,"
                + TravelerDB.UPLOAD_TIME 	+ " INTEGER,"
                + TravelerDB.CREATED_TIME	+ " INTEGER,"
                + TravelerDB.TAG			+ " INTEGER,"
                + TravelerDB.WORKING_FOLDER_PATH 	+ " TEXT,"

                + TravelerDB.PICTURE_FLAG 	+ " INTEGER,"
                + TravelerDB.PICTURE_THUMB 	+ " BYTES,"
                + TravelerDB.PICTURE_PATH 	+ " TEXT,"
                + TravelerDB.LOCATION_TYPE 	+ " INTEGER,"
                + TravelerDB.LOCATION_LAT 	+ " DOUBLE,"
                + TravelerDB.LOCATION_LON 	+ " DOUBLE,"
                + TravelerDB.LOCATION_ALT 	+ " DOUBLE,"
                + TravelerDB.LOCATION_ACC 	+ " DOUBLE,"
                + TravelerDB.VOICE_FLAG 	+ " INTEGER,"
                + TravelerDB.VOICE_PATH 	+ " TEXT,"
                + TravelerDB.TRACK_ID 	+ " TEXT"

                + ");");
		
		db.execSQL("CREATE TABLE " + TRACKS_TABLE_NAME + " ("
				
                + TravelerDB._ID 			+ " INTEGER PRIMARY KEY,"
                + TravelerDB.UUID 			+ " INTEGER,"
                + TravelerDB.TYPE 			+ " INTEGER,"
                + TravelerDB.TITLE 			+ " TEXT,"
                + TravelerDB.DESCRIPTION 	+ " TEXT,"
                + TravelerDB.UPLOAD_FLAG 	+ " INTEGER,"
                + TravelerDB.UPLOAD_TIME 	+ " INTEGER,"
                + TravelerDB.CREATED_TIME	+ " INTEGER,"
                + TravelerDB.TAG			+ " INTEGER,"
                + TravelerDB.WORKING_FOLDER_PATH 	+ " TEXT,"

                + TravelerDB.TRACK_PATH 	+ " TEXT,"
                + TravelerDB.TRACK_COUNT 	+ " INTEGER,"
                + TravelerDB.TRACK_STATE 	+ " INTEGER"

                + ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String cmd1 = "DROP TABLE IF EXISTS" + POIS_TABLE_NAME;
		db.execSQL(cmd1);
		
		String cmd2 = "DROP TABLE IF EXISTS" + TRACKS_TABLE_NAME;
		db.execSQL(cmd2);
		
        onCreate(db);
		
	}
}
